import openpyxl
from openpyxl.styles import Font, Fill, Border, Side, PatternFill
# 这个代码用来整理东南西北和专名大写问题
# 打开工作簿
import Capitalize
import main

workbook = openpyxl.load_workbook(r'D:\Windows\白塔镇加拼音.xlsx')  # 依据哪一个Excel
# workbook = openpyxl.load_workbook(r'D:\Windows\白塔镇加拼音区分东南西北.xlsx')  # 依据哪一个Excel
excelFileName = "白塔镇加拼音区分东南西北"  # 新生成的Excel名称
excelStrLine = 'A'  # 中文列
excelPyLine = 'C'  # 拼音列
chinese_to_arabic = {'A': 1, 'B': 2, 'C': 3, 'D': 4, 'E': 5, 'F': 6, 'G': 7, 'H': 8, 'I': 9, 'J': 10, 'K': 11,
                     'L': 12, 'M': 13, 'N': 14}
# 获取工作表
sheet = workbook['Sheet1']

# 读取拼音单元格数据
b_column_data = []

# 遍历文字列，判断是否为空
for index, cell in enumerate(sheet[f'{excelPyLine}']):
    if cell.value != "#N/A" or cell.value is not None:  # 检查字符串是否为None
        # 往数组里面存储拼音栏为空的 index和对应 中文信息
        b_column_data.append((index + 1, cell.value))

for index, value in b_column_data[1:]:
    endStr = ""
    if value is None:
        continue
    cell_list = value.split()
    length = len(cell_list) - 1

    cell_list[length] = cell_list[length].capitalize()
    cell_list[0] = cell_list[0].capitalize()
    endStr = Capitalize.capitalize_pinyin(cell_list)

    if length == 0:
        fill_color = 'FFFF00'
        cell = sheet.cell(row=int(index), column=chinese_to_arabic[excelPyLine])
        cell.fill = PatternFill(start_color=fill_color, end_color=fill_color, fill_type='solid')
        endStr = Capitalize.capitalize_lu_pinyin(cell_list)

    for value1 in cell_list[1:]:
        endStr += " " + str(value1)
    sheet.cell(row=int(index), column=chinese_to_arabic[excelPyLine], value=endStr)
workbook.save(rf'D:\Windows\{excelFileName}.xlsx')
